﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human.FormBaoCao
{
    public partial class frm_bclamthemgio : Form
    {
        private AccessData mDB = new AccessData();
        Language lan = new Language();
        private DataSet ds = new DataSet();
        private DataSet dsmoi = new DataSet();
        DataSet ds_manv = new DataSet();
        DataSet ds_ts = new DataSet();
        DataSet ts_nv = new DataSet();
        private DataSet ds_vt = new DataSet();
        private DataSet dsmoi_vt = new DataSet();
        DataSet ds_manv_vt = new DataSet();
        DataSet ds_ts_vt = new DataSet();
        DataSet ts_nv_vt = new DataSet();
        string  s_bophan="",user_dn="", thang = "", nam = "", s_mmyy = "", tungay = "", denngay = "", m_user = "", sql = "", title = "", table1 = "", table2 = "", s_mmyy1 = ""
        , s_ncuoithang = "";
        bool bvantay = false;
        public frm_bclamthemgio(string s_makp,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
        }

        private void frm_bclamthemgio_Load(object sender, EventArgs e)
        {
            m_user = mDB.user;
            get_dsmaca();
            title = lan.Change_language_MessageText("GIẤY BÁO LÀM THÊM GIỜ");
            table1 = "ngoaigio";
            table2 = "ccng";
            haison_ng_Validated(null, null);
        }        
        private bool kiemtra()
        {
            tungay = haison_ng.tungay;
            denngay = haison_ng.denngay;
            DateTime dt1 = mDB.StringToDate(tungay);
            DateTime dt2 = mDB.StringToDate(denngay);
            int y1 = dt1.Year, m1 = dt1.Month;
            int y2 = dt2.Year, m2 = dt2.Month;
            int itu, iden;

            string mmyy = "";
            for (int i = y1; i <= y2; i++)
            {
                itu = (i == y1) ? m1 : 1;
                iden = (i == y2) ? m2 : 12;
                for (int j = itu; j <= iden; j++)
                {
                    mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
                    bvantay = mDB.bVantay(mmyy);
                    if (mDB.bMmyy(mmyy) && bvantay==false)
                    {
                        s_mmyy = mmyy;
                        ds_manv.Merge(get_nhanvien(mmyy));
                        getdata(mmyy, denngay);
                        ds_ts.Merge(dsmoi);
                    }
                    if (mDB.bMmyy(mmyy) && bvantay == true)
                    {
                        s_ncuoithang = mDB.get_ncuoithangddmmyyyy(j.ToString().PadLeft(2, '0'), i.ToString());
                        DateTime d1 = Convert.ToDateTime(s_ncuoithang.Substring(3, 2) + "/" + s_ncuoithang.Substring(0, 2) + "/" + s_ncuoithang.Substring(6, 4));
                        s_mmyy1 = d1.AddMonths(1).ToString("MMyy");
                        s_mmyy = mmyy;
                        if (bvantay == true)
                            table2 = "ccvao";
                        else
                            table2 = "ccng";
                        ds_manv_vt.Merge(get_nhanvien_vt(mmyy));
                        get_nvccngoaigio_vt(mmyy);
                        ds_ts_vt.Merge(dsmoi_vt);
                    }
                }              
            }
            return true;
        }
        private void haison_ng_Validated(object sender, EventArgs e)
        {
            ds_manv.Clear(); ds_manv_vt.Clear();
            tungay = haison_ng.tungay;
            denngay = haison_ng.denngay;
            DateTime dt1 = mDB.StringToDate(tungay);
            DateTime dt2 = mDB.StringToDate(denngay);
            int y1 = dt1.Year, m1 = dt1.Month;
            int y2 = dt2.Year, m2 = dt2.Month;
            int itu, iden;

            string mmyy = "";
            for (int i = y1; i <= y2; i++)
            {
                itu = (i == y1) ? m1 : 1;
                iden = (i == y2) ? m2 : 12;
                for (int j = itu; j <= iden; j++)
                {
                    mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
                    bvantay = mDB.bVantay(mmyy);
                    if (mDB.cc_thuongquy(mmyy) == "1")
                        rad_thuongquy.Enabled = true;
                    if (mDB.bMmyy(mmyy) && bvantay==false)
                    {
                        s_mmyy = mmyy;
                        ds_manv.Merge(get_nhanvien(mmyy));
                    }
                    if (mDB.bMmyy(mmyy) && bvantay == true)
                    {
                        s_ncuoithang = mDB.get_ncuoithangddmmyyyy(j.ToString().PadLeft(2, '0'), i.ToString());
                        DateTime d1 = Convert.ToDateTime(s_ncuoithang.Substring(3, 2) + "/" + s_ncuoithang.Substring(0, 2) + "/" + s_ncuoithang.Substring(6, 4));
                        s_mmyy1 = d1.AddMonths(1).ToString("MMyy");
                        s_mmyy = mmyy;
                        if (bvantay == true)
                            table2 = "ccvao";
                        else
                            table2 = "ccng";
                        ds_manv_vt.Merge(get_nhanvien_vt(mmyy));
                    }
                }
            }
            if (bvantay)
            {
                load_nhanvienvt();
            }
            else
            {
                load_nhanvien();
            }
        }
        private DataSet get_nhanvien(string s_mmyy)
        {
            sql = " select distinct l.manv,trim(ho)||' '||trim(ten) as hoten ";
            sql += " from nhansu.llnv l inner join nhansu."+table2+"_" + s_mmyy + " ng on l.manv=ng.manv";
            sql += " where to_timestamp(to_char(ng.ngaybatdau,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('"+tungay+"','dd/mm/yyyy')";
            sql += " and to_timestamp(to_char(ng.ngayketthuc,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + denngay + "','dd/mm/yyyy')";
            return mDB.get_data(sql);
        }
       
        private void load_nhanvien()
        {
            ds_manv = mDB.removeduplicate(ds_manv, "manv");
            lst_manv.DataSource = ds_manv.Tables[0];
            lst_manv.DisplayMember = "manv";
            lst_manv.ValueMember = "hoten";
        }
        private void load_nhanvienvt()
        {
            ds_manv_vt = mDB.removeduplicate(ds_manv_vt, "manv");
            lst_manv.DataSource = ds_manv_vt.Tables[0];
            lst_manv.DisplayMember = "manv";
            lst_manv.ValueMember = "hoten";
        }
        private void btn_Thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        
        public DataSet getdata(string s_mmyy,string s_den)       
        {
            string sql = " select a.manv,a.hoten,b.ten as chucdanh,hscc,letet,t7cn,c.congviec,e.tenbp,";
            sql += " to_char(c.batdau,'hh24:mi') as batdau,";
            sql += "to_char(c.ketthuc,'hh24:mi')as ketthuc,c.ngay,e.sothutu as stt_bp,b.thutu as stt_cd ";
            sql += " ,to_char(c.ngaybatdau,'dd/mm/yyyy') as ngaybatdau, ";
            sql += " to_char(c.ngayketthuc,'dd/mm/yyyy') as ngayketthuc ";
            sql += " ,to_timestamp(to_char(ngaybatdau,'dd/mm/yyyy'),'dd/mm/yyyy') as d_batdau";
            sql += " ,to_timestamp(to_char(ngayketthuc,'dd/mm/yyyy'),'dd/mm/yyyy') as d_ketthuc";
            sql += " ,date_part('hour',batdau) as g_bd,date_part('minute',batdau) as p_bd";
            sql += " ,date_part('hour',ketthuc) as g_kt,date_part('minute',ketthuc) as p_kt";
            sql += " from " + m_user + "."+table1+"_" + s_mmyy + " a ";
            sql += " inner join " + m_user + "."+table2+"_" + s_mmyy + " c on a.manv=c.manv ";
            sql += " join " + m_user + ".llnv d on d.manv=a.manv ";
            sql += " join " + m_user + ".dmcd b on b.ma=d.macd ";
            sql += " join " +m_user+ ".bophan e on e.madv=d.madv ";
            if (txt_manv.Text.Trim() != "")
                sql += " and a.manv ='" + txt_manv.Text.Trim() + "'";
            sql += " and (to_timestamp(to_char(ngaybatdau,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + tungay + "','dd/mm/yyyy') ";
            sql += " and to_timestamp(to_char(ngayketthuc,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            sql += " order by d_batdau,e.sothutu,b.thutu,a.manv";
            ds = mDB.get_data(sql);
            string sql1 = " select '' as ngaythang,'' as congviecdl,'' as tugio,'' as dengio,0.0 as tongsogio,'' dgia,'' as thanhtien,'' as bophan,'' as hoten";
            dsmoi = mDB.get_data(sql1);
            DataRow rnew;
            if (ds.Tables[0].Rows.Count > 0)
            {
                dsmoi.Tables[0].Rows.RemoveAt(0);              
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                   DateTime d_batdau, d_ketthuc;
                   decimal g_bd = 0, g_kt = 0, p_bd = 0, p_kt = 0;                  
                    g_bd = decimal.Parse(dr["g_bd"].ToString());
                    g_kt = decimal.Parse(dr["g_kt"].ToString());
                    p_bd = decimal.Parse(dr["p_bd"].ToString());
                    p_kt = decimal.Parse(dr["p_kt"].ToString());
                    d_batdau=mDB.StringToDate(dr["ngaybatdau"].ToString());
                    d_ketthuc = mDB.StringToDate(dr["ngayketthuc"].ToString());
                    
                    decimal sogio = 0,sogiobd=0,sogiokt=0;
                    
                    if (d_ketthuc == d_batdau)
                    {
                        rnew = dsmoi.Tables[0].NewRow();
                        sogio= Math.Round(((g_kt - g_bd) *60 + (p_kt-p_bd))/60,1);
                        rnew["ngaythang"] = d_batdau.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"]=dr["congviec"];
                        rnew["tugio"]=dr["batdau"];
                        rnew["dengio"]=dr["ketthuc"];
                        rnew["tongsogio"] = sogio;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi.Tables[0].Rows.Add(rnew);
                    }
                    if (d_ketthuc > d_batdau)
                    {
                        rnew = dsmoi.Tables[0].NewRow();
                        sogiobd = Math.Round(((24 - g_bd-1)*60 + (60- p_bd))/60,1);
                        sogiokt = Math.Round(((g_kt * 60) + p_kt) / 60,1);
                        rnew["ngaythang"] = d_batdau.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"] = dr["congviec"];
                        rnew["tugio"] = dr["batdau"];
                        rnew["dengio"] = "24:00";
                        rnew["tongsogio"] = sogiobd;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi.Tables[0].Rows.Add(rnew);
                        //
                        rnew = dsmoi.Tables[0].NewRow();
                        sogiobd = Math.Round(((24 - g_bd - 1) * 60 + (60 - p_bd)) / 60,1);
                        sogiokt = Math.Round(((g_kt * 60) + p_kt) / 60 ,1);
                        rnew["ngaythang"] = d_ketthuc.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"] = dr["congviec"];
                        rnew["tugio"] = "00:00";
                        rnew["dengio"] = dr["ketthuc"];
                        rnew["tongsogio"] = sogiokt;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi.Tables[0].Rows.Add(rnew);                        
                    }                  
                }                
            }
            return dsmoi;
        }
        #region tong hop van tay chi tiet lam them gio
        private DataSet get_nhanvien_vt(string s_mmyy)
        {
            sql = " select distinct nv.manv,trim(nv.ho)||' '||trim(nv.ten) as hoten,nv.madv,bp.tenbp";
            sql += " ,'" + s_mmyy + "' as mmyy";
            sql += " from " + mDB.user + ".llnv nv left join " + mDB.user + ".bophan bp on nv.madv=bp.madv";
            sql += " inner join " + mDB.user + ".hopdong hd on nv.manv=hd.manv";
            sql += " inner join " + mDB.user + ".max_lan ml on hd.manv=ml.manv";
            sql += " inner join " + mDB.user + ".ccvao_" + s_mmyy + " cv on nv.manv=cv.manv";
            sql += " inner join " + mDB.user + ".lichlv lv on lv.maca=cv.maca";
            sql += " where hd.lan=ml.lan and (ngaynghi is null or to_timestamp(to_char(ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql += " > to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            sql += " and (nv.manv in (select cv.manv from nhansu.ccvao_" + s_mmyy + " cv))";
            if (rad_ccng.Checked)
            {
                if (fields_ng.Trim() != "")
                    sql += " and to_char(lv.manhom) in ('NG')";
            }
            if (rad_thuongquy.Checked)
            {
                if (fields_tq.Trim() != "")
                    sql += " and to_char(lv.manhom) in ('TQ')";
            }
            sql += " order by nv.manv";
            return mDB.get_data(sql);
        }
        string f_hc = "", f_kh = "", f_tr = "", f_ng = "", f_tq = "", f_tc = "", f_khac = "", fields_hc = "", fields_tr = "", fields_ng = "", fields_tq = "", fields_tc = "", fields_khac = "";

        private void get_dsmaca()
        {
            string sql1 = " select case when manhom='HC' then maca else null end as hc";
            sql1 += " ,case when manhom='TR' then maca else null end as tr";
            sql1 += " ,case when manhom='NG' then maca else null end as ng";
            sql1 += " ,case when manhom='TQ' then maca else null end as tq ";
            sql1 += " ,case when manhom='TC' then maca else null end as tc ";
            sql1 += " ,case when manhom='KH' then maca else null end as kh from " + m_user + ".lichlv";
            DataSet ds_ma = mDB.get_data(sql1);

            for (int k = 0; k < ds_ma.Tables[0].Rows.Count; k++)
            {
                if (ds_ma.Tables[0].Rows[k]["hc"].ToString().Trim() != "")
                {
                    f_hc += " '" + ds_ma.Tables[0].Rows[k]["hc"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tr"].ToString().Trim() != "")
                {
                    f_tr += " '" + ds_ma.Tables[0].Rows[k]["tr"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["ng"].ToString().Trim() != "")
                {
                    f_ng += " '" + ds_ma.Tables[0].Rows[k]["ng"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tq"].ToString().Trim() != "")
                {
                    f_tq += " '" + ds_ma.Tables[0].Rows[k]["tq"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["tc"].ToString().Trim() != "")
                {
                    f_tc += " '" + ds_ma.Tables[0].Rows[k]["tc"].ToString().Trim() + "',";
                }
                if (ds_ma.Tables[0].Rows[k]["kh"].ToString().Trim() != "")
                {
                    f_kh += " '" + ds_ma.Tables[0].Rows[k]["kh"].ToString().Trim() + "',";
                }
            }
            // lay danh sach nhom maca 
            fields_hc = f_hc.Length > 0 ? f_hc.Substring(0, f_hc.Length - 1) : "";
            fields_tr = f_tr.Length > 0 ? f_tr.Substring(0, f_tr.Length - 1) : "";
            fields_ng = f_ng.Length > 0 ? f_ng.Substring(0, f_ng.Length - 1) : "";
            fields_tq = f_tq.Length > 0 ? f_tq.Substring(0, f_tq.Length - 1) : "";
            fields_tc = f_tc.Length > 0 ? f_tc.Substring(0, f_tc.Length - 1) : "";
            fields_khac = f_khac.Length > 0 ? f_khac.Substring(0, f_khac.Length - 1) : "";

        }
         private DataSet get_nvccngoaigio_vt(string s_mmyy)
        {
            string sql = "",sql1="";
            sql = " select l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,l.madv,bp.tenbp,bp.sothutu,cd.ten as chucdanh,to_char(cv.maca) as maca";
            sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaylv,cv.ghichu congviec,to_char(cr.ngay,'dd/mm/yyyy') as ngaylv_r";
            sql += " ,to_char(cv.thoigian,'hh24:mi') as batdau,to_char(cr.thoigian,'hh24:mi') as ketthuc";
            sql += " ,to_timestamp(to_char(cv.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_batdau";
            sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaybatdau";
            sql += " ,to_char(cr.ngay,'dd/mm/yyyy') as ngayketthuc";
            sql += " ,to_timestamp(to_char(cr.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_ketthuc";
            sql += " ,date_part('hour',cv.thoigian) as g_bd,date_part('minute',cv.thoigian) as p_bd";
            sql += " ,date_part('hour',cr.thoigian) as g_kt,date_part('minute',cr.thoigian) as p_kt";
            sql += " from " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join " + m_user + ".bophan bp on l.madv=bp.madv";
            sql += " left join " + m_user + ".ccvao_" + s_mmyy + " cv on l.manv=cv.manv";
            sql += " left join " + m_user + ".ccra_" + s_mmyy + " cr on l.manv=cr.manv";
            sql += " left join " + m_user + ".dmcd cd on l.macd=cd.ma";
            sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
            sql += " where hd.lan=ml.lan and cv.maca=cr.maca and cv.kt_ng=cr.kt_ng ";
            sql += " and to_timestamp(to_char(cv.ngay,'yyyy-mm-dd'),'yyyy-mm-dd') = to_timestamp(to_char(cr.ngay,'yyyy-mm-dd'),'yyyy-mm-dd')";
            sql += " and (to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy') ";
            sql += " and to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy')) ";
            sql += " and (to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy')";
            sql += " and to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy'))";
            if (txt_manv.Text.Trim() != "")
                sql += " and l.manv ='" + txt_manv.Text.Trim() + "'";
            if (rad_ccng.Checked)
            {
                sql += " and cv.kt_ng <>0 ";
                if (fields_ng.Trim() != "")
                    sql += " and to_char(cv.maca) in (" + fields_ng.Trim() + ")";
                sql += " union";
                sql += " select l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,l.madv,bp.tenbp,bp.sothutu,cd.ten as chucdanh,to_char(cv.maca) as maca";
                sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaylv,cv.ghichu congviec, to_char(cr.ngay,'dd/mm/yyyy') as ngaylv_r";
                sql += " ,to_char(cv.thoigian,'hh24:mi') as batdau,to_char(cr.thoigian,'hh24:mi') as ketthuc";
                sql += " ,to_timestamp(to_char(cv.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_batdau";
                sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaybatdau";
                sql += " ,to_char(cr.ngay,'dd/mm/yyyy') as ngayketthuc";
                sql += " ,to_timestamp(to_char(cr.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_ketthuc";
                sql += " ,date_part('hour',cv.thoigian) as g_bd,date_part('minute',cv.thoigian) as p_bd";
                sql += " ,date_part('hour',cr.thoigian) as g_kt,date_part('minute',cr.thoigian) as p_kt";
                sql += " from " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on l.manv=hd.manv";
                sql += " inner join " + m_user + ".bophan bp on l.madv=bp.madv";
                sql += " left join " + m_user + ".ccvao_" + s_mmyy + " cv on l.manv=cv.manv";
                sql += " left join " + m_user + ".ccra_" + s_mmyy + " cr on l.manv=cr.manv";
                sql += " left join " + m_user + ".dmcd cd on l.macd=cd.ma";
                sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
                sql += " where hd.lan=ml.lan and cv.maca=cr.maca and cv.kt_ng=cr.kt_ng and cv.kt_ng =0";
                sql += " and date_part('day', age(to_timestamp(to_char(cr.ngay,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(cv.ngay,'yyyy-mm-dd'),'yyyy-mm-dd')))=1";
                sql += " and (to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy') ";
                sql += " and to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy')) ";
                sql += " and (to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy')";
                sql += " and to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy'))";
                if (txt_manv.Text.Trim() != "")
                    sql += " and l.manv ='" + txt_manv.Text.Trim() + "'";
                if (rad_ccng.Checked)
                {
                    if (fields_ng.Trim() != "")
                        sql += " and to_char(cv.maca) in (" + fields_ng.Trim() + ")";
                }
                if (rad_thuongquy.Checked)
                {
                    if (fields_tq.Trim() != "")
                        sql += " and to_char(cv.maca) in (" + fields_tq.Trim() + ")";
                }
               
                if (mDB.bMmyy(s_mmyy1))
                {
                    sql += " union";
                    sql += " select l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,l.madv,bp.tenbp,bp.sothutu,cd.ten as chucdanh,to_char(cv.maca) as maca";
                    sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaylv,cv.ghichu congviec, to_char(cr.ngay,'dd/mm/yyyy') as ngaylv_r";
                    sql += " ,to_char(cv.thoigian,'hh24:mi') as batdau,to_char(cr.thoigian,'hh24:mi') as ketthuc";
                    sql += " ,to_timestamp(to_char(cv.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_batdau";
                    sql += " ,to_char(cv.ngay,'dd/mm/yyyy') as ngaybatdau";
                    sql += " ,to_char(cr.ngay,'dd/mm/yyyy') as ngayketthuc";
                    sql += " ,to_timestamp(to_char(cr.ngay,'yyyy-dd-mm'),'yyyy-dd-mm') as d_ketthuc";
                    sql += " ,date_part('hour',cv.thoigian) as g_bd,date_part('minute',cv.thoigian) as p_bd";
                    sql += " ,date_part('hour',cr.thoigian) as g_kt,date_part('minute',cr.thoigian) as p_kt";
                    sql += " from " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on l.manv=hd.manv";
                    sql += " inner join " + m_user + ".bophan bp on l.madv=bp.madv";
                    sql += " inner join " + m_user + ".ccvao_" + s_mmyy + " cv on l.manv=cv.manv";
                    sql += " inner join " + m_user + ".ccra_" + s_mmyy1 + " cr on cv.manv=cr.manv";
                    sql += " left join " + m_user + ".dmcd cd on l.macd=cd.ma";
                    sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
                    sql += " where hd.lan=ml.lan and cv.maca=cr.maca and cv.kt_ng=cr.kt_ng and cv.kt_ng =0";
                    sql += " and to_timestamp(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')=to_timestamp('" + s_ncuoithang.Trim() + "','dd/mm/yyyy')";
                    sql += " and date_part('day', age(to_timestamp(to_char(cr.ngay,'yyyy-mm-dd'),'yyyy-mm-dd'),to_timestamp(to_char(cv.ngay,'yyyy-mm-dd'),'yyyy-mm-dd')))=1";
                    sql += " and (to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy') ";
                    sql += " and to_date(to_char(cv.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy')) ";
                    sql += " and (to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')<= to_date('" + denngay + "','dd/mm/yyyy')";
                    sql += " and to_date(to_char(cr.ngay,'dd/mm/yyyy'),'dd/mm/yyyy')>= to_date('" + tungay + "','dd/mm/yyyy'))";
                    if (txt_manv.Text.Trim() != "")
                        sql += " and l.manv ='" + txt_manv.Text.Trim() + "'";
                    if (rad_ccng.Checked)
                    {
                        if (fields_ng.Trim() != "")
                            sql += " and to_char(cv.maca) in (" + fields_ng.Trim() + ")";
                    }
                    if (rad_thuongquy.Checked)
                    {
                        if (fields_tq.Trim() != "")
                            sql += " and to_char(cv.maca) in (" + fields_tq.Trim() + ")";
                    }
                   
                }
            }
            if (rad_thuongquy.Checked)
            {
                if (fields_tq.Trim() != "")
                    sql += " and to_char(cv.maca) in (" + fields_tq.Trim() + ")";
            }
            ds_vt = mDB.get_data(sql);
            sql1 = " select '' as ngaythang,'' as congviecdl,'' as tugio,'' as dengio,0.0 as tongsogio,'' dgia,'' as thanhtien,'' as bophan,'' as hoten";
            dsmoi_vt = mDB.get_data(sql1);
            DataRow rnew;
            if (ds_vt.Tables[0].Rows.Count > 0)
            {
                dsmoi_vt.Tables[0].Rows.RemoveAt(0);
                foreach (DataRow dr in ds_vt.Tables[0].Rows)
                {
                    DateTime d_batdau, d_ketthuc;
                    decimal g_bd = 0, g_kt = 0, p_bd = 0, p_kt = 0;
                    g_bd = decimal.Parse(dr["g_bd"].ToString());
                    g_kt = decimal.Parse(dr["g_kt"].ToString());
                    p_bd = decimal.Parse(dr["p_bd"].ToString());
                    p_kt = decimal.Parse(dr["p_kt"].ToString());
                    d_batdau = mDB.StringToDate(dr["ngaybatdau"].ToString());
                    d_ketthuc = mDB.StringToDate(dr["ngayketthuc"].ToString());

                    decimal sogio = 0, sogiobd = 0, sogiokt = 0;

                    if (d_ketthuc == d_batdau)
                    {
                        rnew = dsmoi_vt.Tables[0].NewRow();
                        sogio = Math.Round(((g_kt - g_bd) * 60 + (p_kt - p_bd)) / 60, 1);
                        rnew["ngaythang"] = d_batdau.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"] = dr["congviec"];
                        rnew["tugio"] = dr["batdau"];
                        rnew["dengio"] = dr["ketthuc"];
                        rnew["tongsogio"] = sogio;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi_vt.Tables[0].Rows.Add(rnew);
                    }
                    if (d_ketthuc > d_batdau)
                    {
                        rnew = dsmoi_vt.Tables[0].NewRow();
                        sogiobd = Math.Round(((24 - g_bd - 1) * 60 + (60 - p_bd)) / 60, 1);
                        sogiokt = Math.Round(((g_kt * 60) + p_kt) / 60, 1);
                        rnew["ngaythang"] = d_batdau.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"] = dr["congviec"];
                        rnew["tugio"] = dr["batdau"];
                        rnew["dengio"] = "24:00";
                        rnew["tongsogio"] = sogiobd;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi_vt.Tables[0].Rows.Add(rnew);
                        //
                        rnew = dsmoi_vt.Tables[0].NewRow();
                        sogiobd = Math.Round(((24 - g_bd - 1) * 60 + (60 - p_bd)) / 60, 1);
                        sogiokt = Math.Round(((g_kt * 60) + p_kt) / 60, 1);
                        rnew["ngaythang"] = d_ketthuc.Date.ToString("dd/MM/yyyy");
                        rnew["congviecdl"] = dr["congviec"];
                        rnew["tugio"] = "00:00";
                        rnew["dengio"] = dr["ketthuc"];
                        rnew["tongsogio"] = sogiokt;
                        rnew["bophan"] = dr["tenbp"];
                        rnew["hoten"] = dr["hoten"];
                        dsmoi_vt.Tables[0].Rows.Add(rnew);
                    }
                }
            }
            else
            {
                //MessageBox.Show("Chưa có dữ liệu", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);              
            }
            return dsmoi_vt;
        }
        #endregion
        private void print(int prn)
        {
            if (ds_ts.Tables[0].Rows.Count > 0)
            {
                ds_ts.WriteXml("..\\xml\\h_bclamthemgio.xml", XmlWriteMode.WriteSchema);
                try
                {
                    switch (prn)
                    {
                        case 1: frmReport f = new frmReport(mDB, ds_ts, "h_bclamthemgio.rpt", title, lan.Change_language_MessageText("(Từ ngày " )+ tungay +lan.Change_language_MessageText( " đến ngày ") + denngay + ")",user_dn); 
                            f.ShowDialog();
                            break;
                    }
                }
                catch { }
            }
            
            ds_ts.Clear(); 
        }
        private void print1()
        {
            if (ds_ts_vt.Tables[0].Rows.Count > 0)
            {
                ds_ts_vt.WriteXml("..\\xml\\h_bclamthemgiovt.xml", XmlWriteMode.WriteSchema);
                frmReport f = new frmReport(mDB, ds_ts_vt, "h_bclamthemgiovt.rpt", title, lan.Change_language_MessageText("(Từ ngày ") + tungay + lan.Change_language_MessageText(" đến ngày ") + denngay + ")");
                f.ShowDialog();
                
            }
            ds_ts_vt.Clear();
        }
        private void btn_Xem_Click(object sender, EventArgs e)
        {
            kiemtra();
            
            if (txt_manv.Text.Trim()=="")
            {
                  MessageBox.Show(lan.Change_language_MessageText("Bạn phải chọn đối tượng cần xem !!!"));
                  txt_hoten.Focus();
            }
            else
            if (bvantay)
            {
                print1();
            }
            else
            {
                print(1);
            }
        }        
        private void btn_Xem_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}{F4}");
        }
        private void btn_exel_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}{F4}");
        }
        private void dtp_denngay_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}{F4}");
        }
        private void loc_hoten(string hoten)
        {
            try
            {
                CurrencyManager cm = (CurrencyManager)this.BindingContext[lst_manv.DataSource];
                DataView dv = (DataView)cm.List;
                dv.RowFilter = "hoten like '%" + hoten.Trim() + "%' ";
            }
            catch
            {

            }
        }
        private void txt_hoten_TextChanged(object sender, EventArgs e)
        {
            if (this.ActiveControl == this.txt_hoten)
            {
                this.loc_hoten(txt_hoten.Text.Trim());
                this.lst_manv.BrowseToTennv(txt_hoten, txt_manv, btn_Xem, 300);
            }
        }

        private void txt_hoten_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyCode == Keys.Down) || (e.KeyCode == Keys.Up))
            {
                this.lst_manv.Focus();
            }
            else if (e.KeyCode == Keys.Return)
            {
                if (this.lst_manv.Visible)
                {
                    this.lst_manv.Focus();
                }
                else
                {
                    SendKeys.Send("{Tab}");
                   
                }
            }
        }
        DataRow[] dr_nv; DataRow[] dr_nv_vt;
        private void txt_manv_Validated(object sender, EventArgs e)
        {
            if (bvantay == false)
            {
                if (txt_manv.Text.Trim() != "")
                {
                    this.dr_nv = ds_manv.Tables[0].Select("manv='" + txt_manv.Text.Trim() + "'");
                    if (dr_nv != null)
                    {
                        txt_hoten.Text = dr_nv[0]["hoten"].ToString().Trim();
                    }
                    else
                    {
                        txt_hoten.Text = "";
                    }
                }
            }
            else
            {
                if (txt_manv.Text.Trim() != "")
                {
                    this.dr_nv_vt = ds_manv_vt.Tables[0].Select("manv='" + txt_manv.Text.Trim() + "'");
                    if (dr_nv_vt != null)
                    {
                        txt_hoten.Text = dr_nv_vt[0]["hoten"].ToString().Trim();
                    }
                    else
                    {
                        txt_hoten.Text = "";
                    }
                }
            }
        }
        private void lst_manv_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Return)
            {
                txt_manv_Validated(null, null);
                btn_Xem.Enabled = true;
            }
        }
        private void haison_ng_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                txt_hoten.Focus();
        }
        private void rad_ccng_CheckedChanged(object sender, EventArgs e)
        {
           
            if (rad_ccng.Checked && bvantay==false)
            {
                title = lan.Change_language_MessageText("GIẤY BÁO LÀM THÊM GIỜ");
                table1 = "ngoaigio";
                table2 = "ccng";
            }
            if (rad_ccng.Checked && bvantay==true)
            {
                title = lan.Change_language_MessageText("GIẤY BÁO LÀM THÊM GIỜ VÂN TAY ");
                table2 = "ccvao";
            }
             haison_ng_Validated(null, null);
        }

        private void rad_thuongquy_CheckedChanged(object sender, EventArgs e)
        {
            
            if (rad_thuongquy.Checked && bvantay==false)
            {
                title = lan.Change_language_MessageText("GIẤY BÁO LÀM THÊM GIỜ THƯỜNG QUY");
                table1 = "thuongquy";
                table2 = "chamcong_thuongquy";
            }
            if (rad_thuongquy.Checked && bvantay==true)
            {
                title = lan.Change_language_MessageText("GIẤY BÁO LÀM THÊM GIỜ THƯỜNG QUY VÂN TAY ");
                table2 = "ccvao";
            } 
            haison_ng_Validated(null, null);
        }

        
    }
}